package com.szholly.pro.watergas_server.ps.service;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.RowBase;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 * 运维任务服务
 * @author Administrator
 *
 */
@Service
public class OperationTaskService {
	/**
	 * 获取运维任务
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getTaskList(Map<String,String> params,PageInfo pageInfo) throws Exception {
		String sql = " select t1.*,t8.is_timeout,t2.STATION_ONOFF,t2.WWTP_ID as PS_WWTP_ID,t2.name as STATION_NAME,t3.job_name, "
				+ " t6.TASK_TOLERANCE,t4.group_name,"
				+ "(case t1.Is_Unusual when '1' then '有|查看异常记录' else '无' end) as Is_Unusual_desc,t7.c_userealname as username  "//,t5.name as WWTP_NAME
				+ "from OP_TASK t1 "
				+ "left join BC_SITE_INFO t2 on t1.station_id=t2.exp_no "
				+ "left join OPERATION_JOB t3 on t1.job_id=t3.guid "
				+ "left join OC_GROUP t4 on t1.OPERATION_GROUP_ID=t4.guid "
				//+ "left join BC_ENTERPRISE_ATTR t5 on t2.wwtp_id=t5.exp_no "由于产生两条数据暂时屏蔽
				+ "left join OC_TASK_CONFIG t6 on t1.task_code=t6.CURRENT_TASK_CODE "
				+ "left join safe_user t7 on t1.finish_by=t7.s_userid  left join OP_EFFE t8 on t1.task_code=t8.task_code where 1=1 ";
		
		TwoTuple<String,List<GlobalParameter>> p = getTaskQueryParams(params);
		sql += p.first;
		if(params.get("water_type").equals("3")){
			sql += " order by to_number(t1.station_id),t1.start_time desc";
		}
		else{
			sql += " order by t1.station_id,t1.start_time desc";
		}
		List<GlobalParameter> listParam = p.second;
		
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");
		
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}
	/**
	 * 运维统计
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> TaskStatistics(Map<String,String> params)throws Exception{
		String sql="select ms.name,count(ot.guid) as opercount from BC_SITE_INFO ms"
      +" left join OP_TASK ot on ms.code = ot.station_id and ot.task_status = '1'";
      if (params.containsKey("start")) {//开始时间
			sql += " and ot.start_time >= to_date('" + params.get("start")
					+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if (params.containsKey("end")) {//结束时间
			sql += " and ot.end_time <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}		      
		sql +=" left join OPERATION_JOB job on ot.job_id=job.guid  where ms.station_category = '3' and  ms.build_status='0' and job.job_code='gsdqwh'";		
		if(params.containsKey("station_id")) {
			sql += " and ms.code='"+params.get("station_id")+"'";
		}
		sql+=" group by ms.name,ot.operation_group_id order by opercount desc ";
		return DataUtil.getSelectRows(sql, null);
	}
	/**
	 * 运维任务导入
	 * @param tempPath
	 */
	public void importExcel(String tempPath){
		 try  
	        {  			  
			 	URL url = new URL(tempPath);  
			 	URLConnection conn = url.openConnection();
	            Workbook wb = Workbook.getWorkbook(conn.getInputStream());  
	            Sheet sheet = wb.getSheet(0);  
	            //获取表的行数  
	            int row = sheet.getRows();  
	            //循环获取值  
	            for(int i =1;i<row;i++)  
	            {  
	                String StationCode = sheet.getCell(0,i).getContents().replace(" ", ""); 
	                String jobId = sheet.getCell(1,i).getContents().replace(" ", "");  
	                String groupName = sheet.getCell(2,i).getContents().replace(" ", "");  
	                String startTime = sheet.getCell(3,i).getContents().replace(" ", ""); 
	                String sql=" insert into OP_TASK(guid,station_id,operation_group_id,task_status,start_time,end_time,job_id)values"
	                		  +" (sys_guid(),'"+StationCode+"',"
	                		  + "( select guid from OC_GROUP t where t.group_name='"+groupName+"'),"
	                		  + "'0',to_date('"+startTime+" 00:00:00','yyyy-mm-dd hh24:mi:ss')"
	                		  +" ,to_date('"+startTime+" 23:59:59','yyyy-mm-dd hh24:mi:ss'),( select guid from operation_job t where t.job_name='"+jobId+"' and t.job_type='3'))";
	                DataUtil.ExecuteUpdateSql(sql, null);
	            } 
	            DataUtil.commitTransaction();	
	   
	        } catch (BiffException e)  
	        {  
	            e.printStackTrace();  
	        } catch (IOException e)  
	        {   
	            e.printStackTrace();  
	        }
	}
	/**
	 * 定期维护统计
	 * @param params
	 * @return
	 * @throws Exception 
	 */
	public List<RowBase> getRecordMaintainance(Map<String,String> params){
		String sql="select t1.station_id as CODE,substr(t3.name,instr(t3.name,'、')+1,length(t3.name)-instr(t3.name,'、')) as NAME,"
				+ "t1.Thancount,t1.Thantime,t2.maintaincount,t2.maintaintime from (select t.station_id,count(t.start_time)as Thancount,"
				+ "wmsys.wm_concat(distinct to_char(t.start_time,'yyyy-mm-dd hh24:mi:ss')) as Thantime"
				  +" from OP_TASK  t where t.job_id ='6e0d4a4d-c107-4645-b3b2-1c1068f0251b' and t.task_status in('1','3')";
			if(params.containsKey("station_id")){
			   sql+=" and t.station_id='"+params.get("station_id")+"'";
			}
			if(params.containsKey("start")){
			   sql+=" and t.start_time >=to_date('"+params.get("start")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";
			}
			if(params.containsKey("end")){
			   sql+=" and t.start_time <=to_date('"+params.get("end")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";	
			}
			   sql+=" group by t.station_id order by to_number(t.station_id) asc) t1 left join"
				  +" (select t.station_id,count(t.start_time)as maintaincount ,wmsys.wm_concat(distinct to_char(t.start_time,'yyyy-mm-dd hh24:mi:ss')) as maintaintime"
				  +" from OP_TASK  t where t.job_id ='16fe2eed-eba1-4b1c-8ece-46a725a5797b'and t.task_status in('1','3')";
			if(params.containsKey("station_id")){
				sql+=" and t.station_id='"+params.get("station_id")+"'";
			}
			if(params.containsKey("start")){
				sql+=" and t.start_time >=to_date('"+params.get("start")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";
			}
		    if(params.containsKey("end")){
				sql+=" and t.start_time <=to_date('"+params.get("end")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";	
			}
			   sql+=" group by t.station_id order by to_number(t.station_id) asc) t2 on t1.station_id=t2.station_id"
				  +" left join BC_SITE_INFO t3 on t3.code=t1.station_id and t3.station_category='3' order by to_number(t3.code) asc"; 
		return DataUtil.getSelectRows(sql, null);
	}
	/**
	 * 获取任务查询的请求参数
	 * @param params
	 * @return
	 */
	private TwoTuple<String,List<GlobalParameter>> getTaskQueryParams(Map<String,String> params){
		StringBuilder sql = new StringBuilder();
		List<GlobalParameter> listParam = new ArrayList<>();
		
		if (params.containsKey("wwtp_id")) {
			sql.append(" and t2.WWTP_ID=@wwtp_id");
			listParam.add(new GlobalParameter("wwtp_id",params.get("wwtp_id")));
		}
		if (params.containsKey("flow_types")) {
			String flowTypes = params.get("flow_types");
			sql.append(" and t2.flow_type in (" + flowTypes + ")");
		}
		if(params.containsKey("station_id")){
			sql.append(" and t2.EXP_NO=@station_id");
			listParam.add(new GlobalParameter("station_id",params.get("station_id")));
		}
		/*if (params.containsKey("water_type")) {
			sql.append(" and t2.STATION_CATEGORY = @water_type");
			listParam.add(new GlobalParameter("water_type", params.get("water_type")));
		}*/
		if (params.containsKey("stationTypestrids")) {
			sql.append(" and t2.STATION_CATEGORY = @stationTypestrids");
			listParam.add(new GlobalParameter("stationTypestrids", params.get("stationTypestrids")));
		}
		/*if (params.containsKey("stationTypestrids")) {
			String stationTypestrids = params.get("stationTypestrids");
			sql.append(" and t2.STATION_CATEGORY in (" + stationTypestrids + ")");
		}*/
		if (params.containsKey("group_ids")) {
			String groupIds = params.get("group_ids");
			sql.append(" and t1.OPERATION_GROUP_ID in (" + groupIds + ")");
		}
		if (params.containsKey("job_ids")) {
			String jobIds = params.get("job_ids");
			sql.append(" and t1.JOB_ID in (" + jobIds + ")");
		}
		if (params.containsKey("task_status")) {
			String taskStatus = params.get("task_status");
			sql.append(" and t1.TASK_STATUS in (" + taskStatus + ")");
		}
		if (params.containsKey("start")) {
			String start = params.get("start");
			sql.append(" and t1.start_time >= to_date('" + start + " 00:00:00','yyyy-mm-dd HH24:MI:SS') ");
		}
		if (params.containsKey("end")) {
			String end = params.get("end");
			sql.append(" and t1.start_time <= to_date('" + end + " 23:59:59','yyyy-mm-dd HH24:MI:SS') ");
		}
		
		return new TwoTuple<String, List<GlobalParameter>>(sql.toString(),listParam);
	}
}
